﻿/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Diagnostics;
using System.Globalization;

namespace IvyOrm
{
    /// <summary>
    /// Provides ValueQuery, ValueSingle and ValueSingleOrDefault extensions to the DbConnection and DbTransaction object.
    /// </summary>
    public static class ValueQueryExtension
    {
        #region ValueQuery<T>
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T[] ValueQuery<T>(this DbConnection connection, string commandText, params object[] args)
        {
            return ValueQuery<T>(connection, new IvyOrmOptions(), commandText, args);
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T[] ValueQuery<T>(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ValueQuery<T>(transaction, new IvyOrmOptions(), commandText, args);
        }


        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T[] ValueQuery<T>(this DbConnection connection, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = connection.CreateCommand())
            using (var commandBuilder = connection.GetFactory().CreateCommandBuilder())
            {
                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 1879045, "ValueQuery with the following command: \n" + command.CommandText);

                using (var reader = command.ExecuteReader())
                {
                    return read<T>(options, reader, false);
                }
            }
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T[] ValueQuery<T>(this DbTransaction transaction, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("connection property must be set", "transaction");

            options = IvyOrmOptions.Defaults(transaction.Connection, options);

            using (var command = options.DbProviderFactory.CreateCommand())
            using (var commandBuilder = options.DbProviderFactory.CreateCommandBuilder())
            {
                command.Connection = transaction.Connection;
                command.Transaction = transaction;

                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 1879045, "ValueQuery with the following command: \n" + command.CommandText);

                using (var reader = command.ExecuteReader())
                    return read<T>(options, reader, false);
            }
        }
        #endregion

        #region ValueSingle<T>
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>        
        public static T ValueSingle<T>(this DbConnection connection, string commandText, params object[] args)
        {
            return ValueSingle<T>(connection,  new IvyOrmOptions(), commandText, args);
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>        
        public static T ValueSingle<T>(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ValueSingle<T>(transaction, new IvyOrmOptions(), commandText, args);
        }

        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingle<T>(this DbConnection connection, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = options.DbProviderFactory.CreateCommand())
            using (var commandBuilder = options.DbProviderFactory.CreateCommandBuilder())
            {
                command.Connection = connection;
                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 1879045, "ValueSingle with the following command: \n" + command.CommandText);

                return readSingle<T>(options, command);
            }
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingle<T>(this DbTransaction transaction, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("connection property must be set", "transaction");

            options = IvyOrmOptions.Defaults(transaction.Connection, options);

            using (var command = options.DbProviderFactory.CreateCommand())
            using (var commandBuilder = options.DbProviderFactory.CreateCommandBuilder())
            {
                command.Connection = transaction.Connection;
                command.Transaction = transaction;
                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 1879045, "ValueSingle with the following command: \n" + command.CommandText);

                return readSingle<T>(options, command);
            }
        }
        #endregion

        #region ValueSingleOrDefault<T>
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingleOrDefault<T>(this DbConnection connection, string commandText, params object[] args)
        {
            return ValueSingleOrDefault<T>(connection, new IvyOrmOptions(), commandText, args);
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingleOrDefault<T>(this DbTransaction transaction, string commandText, params object[] args)
        {
            return ValueSingleOrDefault<T>(transaction, new IvyOrmOptions(), commandText, args);
        }

        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingleOrDefault<T>(this DbConnection connection, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            options = IvyOrmOptions.Defaults(connection, options);

            using (var command = options.DbProviderFactory.CreateCommand())
            using (var commandBuilder = options.DbProviderFactory.CreateCommandBuilder())
            {
                command.Connection = connection;

                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 0, "ValueSingleOrDefault with the following command: \n" + command.CommandText);

                return readSingleOrDefault<T>(options, command);
            }
        }
        /// <summary>
        /// Executes the query and returns the value from the first 
        /// column of the result set. All other columns are ignored. Value is converted 
        /// to T.
        /// 
        /// ValueQuery returns all rows.
        /// ValueSingle returns only the first row and throws an exception if no rows are returned.
        /// ValueSingleOrDefault returns only the first row and returns default(T) when no rows are returned.
        /// </summary>
        /// <typeparam name="T">The target value to return</typeparam>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="commandText">
        /// The command to execute including parameters placeholders formatted as @0, @1, ... placeholders will be
        /// converted to placeholders compatible with the database.
        /// </param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="args">An object array that contains zero or more objects to as database parameters values.</param>
        /// <returns>Value(s) converted from the data set to type T</returns>
        public static T ValueSingleOrDefault<T>(this DbTransaction transaction, IvyOrmOptions options, string commandText, params object[] args)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("connection property must be set", "transaction");

            options = IvyOrmOptions.Defaults(transaction.Connection, options);

            using (var command = options.DbProviderFactory.CreateCommand())
            using (var commandBuilder = options.DbProviderFactory.CreateCommandBuilder())
            {
                command.Connection = transaction.Connection;
                command.Transaction = transaction;

                ExecuteCommandExtension.prepairCommand(command, commandBuilder, commandText, args);

                Logging.Log(options, TraceEventType.Information, 0, "ValueSingleOrDefault with the following command: \n" + command.CommandText);

                return readSingleOrDefault<T>(options, command);
            }
        }
        #endregion

        private static T[] read<T>(IvyOrmOptions options, DbDataReader reader, bool single)
        {
            ValueConverterFactory converterFactory = options.ValueConverterFactory;
            Debug.Assert(converterFactory != null);

            var sourceType = reader.GetFieldType(0);
            var destinationType = typeof(T);
            var convert = converterFactory.GetFromDatabaseConverter(sourceType, destinationType);
            if (convert == null)
                throw new NotSupportedException("Converter not available for " + sourceType.FullName + " to " + destinationType.FullName + " conversion");

            List<T> results = new List<T>();

            while (reader.Read())
            {
                var value = reader[0];
                if (value == DBNull.Value)
                    value = null;

                results.Add((T)convert(value));
                if (single)
                    break;
            }

            return results.ToArray();
        }
        private static T readSingle<T>(IvyOrmOptions options,  DbCommand command)
        {
            Debug.Assert(options != null);
            Debug.Assert(command != null);

            using (var reader = command.ExecuteReader(CommandBehavior.SingleRow))
            {
                var results = read<T>(options, reader, true);
                if (results.Length == 0)
                {
                    var exception = new IvyOrmException("Expecting one or more results, no results returned");
                    Logging.Log(options, TraceEventType.Error, 0, exception.Message, exception);
                    throw exception;
                }

                return results[0];
            }
        }
        private static T readSingleOrDefault<T>(IvyOrmOptions options, DbCommand command)
        {
            Debug.Assert(options != null);
            Debug.Assert(command != null);

            using (var reader = command.ExecuteReader(CommandBehavior.SingleRow))
            {
                var results = read<T>(options, reader, true);
                if (results.Length == 0)
                    return default(T);
                else
                    return results[0];
            }
        }
    }
}
